package com.rent.Impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.rent.bean.Car;
import com.rent.dao.CarDao;
import com.rent.utils.BaseDao;
import com.rent.utils.DataAccessUtils;

public class CarDaoImpl extends BaseDao implements CarDao {

	@Override
	public int updateCar(Car c) {
		StringBuffer sql = new StringBuffer("update car_new set carid=carid ");
		if(c.getCstatus()!=0) {
			sql.append(",cstatus="+c.getCstatus());
		}
		if(c.getCgrade()!=0) {
			sql.append(",cgrade="+c.getCgrade());
		}
		if(c.getCtype()!=null) {
		sql.append(",tid="+c.getTid()+"");
		}
		if(c.getIsdelete()!=-1) {
			sql.append(",isdelete="+c.getIsdelete());
		}
		if(c.getSeat()!=0) {
			sql.append(",seat="+c.getSeat());
		}
		if(c.getGearbox()!=null) {
			sql.append(",gearbox='"+c.getGearbox()+"'");
		}
		if(c.getCarNumber()!=null) {
		sql.append(",carNumber='"+c.getCarNumber()+"'");
		}
		if(c.getEngineNumber()!=null) {
			sql.append(",engineNumber='"+c.getEngineNumber()+"'");
		}
		if(c.getGasolineVolume()!=0) {
			sql.append(",gasolineVolume="+c.getGasolineVolume());
		}
		if(c.getGasolineActualVolume()!=0) {
			sql.append(",gasolineActualVolume="+c.getGasolineActualVolume());
		}
		if(c.getGasolineNumber()!=0) {
			sql.append(",gasolineNumber="+c.getGasolineNumber());
		}
		if(c.getColor()!=null) {
			sql.append(",color='"+c.getColor()+"'");
		}
		sql.append(" where carid=?;");
		return executeUpdate(sql.toString(),c.getCarid());
	}

	DataAccessUtils d = new DataAccessUtils();
	
	@Override
	public List<Car> sele_car(int power,Car c) {
		List<Car> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer();
		if(power==3) {//管理员
			sql.append("select * from car_n where 1=1 ");//全部车，被删除的也包括
		}else if(power==2) {//会员
			sql.append("select * from car_n where cstatus=1 and isdelete=0");
		}else {//普通用户
			sql.append("select * from car_n where cstatus=1 and cgrade=1 and isdelete=0");
		}
		//拼接条件
		if(c.getCarid()!=0) {
			sql.append(" and carid = "+c.getCarid());
		}
		if(c.getCbrand()!=null) {
			sql.append(" and brand = '"+c.getCbrand()+"'");
		}
		if(c.getCtype()!=null) {
			sql.append(" and type = '"+c.getCtype()+"'");
		}
		if(c.getColor()!=null) {
			sql.append(" and color = '"+c.getColor()+"'");
		}
		if(c.getCprice()!=0) {
			sql.append(" and price < "+c.getCprice()+"");
		}
		if(c.getCstatus()!=0) {
			sql.append(" and cstatus = "+c.getCstatus());
		}
		//获取结果
		ResultSet rs = executeQuery(sql.toString());
		try {
			list = d.parseCar(rs);
		} catch (Exception e) {
			// TODO 日志
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return list;
	}
	

	@Override
	public int updateGasolineActualVolume(Car c) {
		String sql = "update car_new set gasolineActualVolume=? where carid = ?;";
		return executeUpdate(sql, c.getGasolineActualVolume(),c.getCarid());
	}


	@Override
	public List<Car> sele_carByType(int power, Car c) {
		List<Car> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer();
		if(power==3) {//管理员
			sql.append("select * from car_n where 1=1 ");//全部车，被删除的也包括
		}else if(power==2) {//会员
			sql.append("select * from car_n where cstatus=1 and isdelete=0");
		}else {//普通用户
			sql.append("select * from car_n where cstatus=1 and cgrade=1 and isdelete=0");
		}
		//拼接条件
		if(c.getCarid()!=0) {
			sql.append(" and carid = "+c.getCarid());
		}
		if(c.getCbrand()!=null) {
			sql.append(" and brand = '"+c.getCbrand()+"'");
		}
		if(c.getCtype()!=null) {
			sql.append(" and type = '"+c.getCtype()+"'");
		}
		//获取结果
		ResultSet rs = executeQuery(sql.toString());
		try {
			list = d.parseCar(rs);
		} catch (Exception e) {
			// TODO 日志
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return list;
	}


	@Override
	public Car seleById(int id) {
		String sql = "select * from car_n where carid = ?";
		ResultSet rs = executeQuery(sql, id);
		Car car = null;
		try {
			if(rs.next()) {
				car = new Car(
						rs.getInt("carid"),
						rs.getString("brand"),
						rs.getDouble("price"),
						rs.getInt("cstatus"),
						rs.getInt("cgrade"),
						rs.getString("type"),
						rs.getInt("isdelete"),
						rs.getInt("seat"),
						rs.getString("gearbox"),
						rs.getString("carNumber"),
						rs.getString("engineNumber"),
						rs.getInt("gasolineVolume"),
						rs.getInt("gasolineActualVolume"),
						rs.getInt("gasolineNumber"),
						rs.getString("color")
					);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return car;
	}

	@Override
	public int updateStatusById(Car c) {
		String sql = "update car_new set cstatus = ? where carid=?;";
		return executeUpdate(sql, c.getCstatus(),c.getCarid());
	}

	@Override
	public int add(Car c) {
		//INSERT INTO `rent`.`car`(`carid`, `cbrand`, `cprice`, `cstatus`, `cgrade`, `ctype`, `isdelete`, `seat`, `gearbox`, `carNumber`, `engineNumber`, `gasolineVolume`, `gasolineActualVolume`, `gasolineNumber`, `color`) VALUES (1, '宝马', 20.00, 2, 2, 'car', 0, 3, '手动挡', '京A88881', '410012Q', 40, 40, 92, '黑色');
		String sql = "INSERT INTO `rent`.`car_new`(`carid`, `cstatus`, `cgrade`, `isdelete`, `seat`, `gearbox`, `carNumber`, `engineNumber`, `gasolineVolume`, `gasolineActualVolume`, `gasolineNumber`, `color`, `tid`) VALUES (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
		return executeUpdate(sql,c.getCstatus(),c.getCgrade(),c.getIsdelete(),c.getSeat(),c.getGearbox(),c.getCarNumber(),c.getEngineNumber(),c.getGasolineVolume(),c.getGasolineActualVolume(),c.getGasolineNumber(),c.getColor(),c.getTid());
	}
	
	
	
	@Override
	public List<Car> seleRank() {
		String sql = "select count(1) num,car_n.* from rent right join car_n on rent.cid=car_n.carid where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(rtime) group by brand,type order by num desc limit 3;";
		List<Car> list = new ArrayList<>();
		ResultSet rs = executeQuery(sql);
		try {
			while(rs.next()) {
				Car car = new Car(
						rs.getInt("carid"),
						rs.getString("brand"),
						rs.getDouble("price"),
						rs.getInt("cstatus"),
						rs.getInt("cgrade"),
						rs.getString("type"),
						rs.getInt("isdelete"),
						rs.getInt("seat"),
						rs.getString("gearbox"),
						rs.getString("carNumber"),
						rs.getString("engineNumber"),
						rs.getInt("gasolineVolume"),
						rs.getInt("gasolineActualVolume"),
						rs.getInt("gasolineNumber"),
						rs.getString("color")
					);
				car.num=rs.getInt("num");
				list.add(car);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(rs);
		}
		return list;
	}
	
}
